package sprinklerbee.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import sprinklerbee.db.DbConn;
import sprinklerbee.model.Group;
import sprinklerbee.model.Sensor;
import sprinklerbee.model.Sprinkler;
import sprinklerbee.model.SprinklerType;

public class SprinklerDAOImpl extends DbConn implements SprinklerDAO{

	@Override
	public void addSprinkler(Sprinkler sprinkler) throws RuntimeException {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO `sprinkler` VALUES(NULL,'"
				+ sprinkler.getName() + "','"
				+ sprinkler.getDescription() + "',NOW()," + sprinkler.getGroup().getId() + ","
				+ sprinkler.getSprinklerType().getId() + ","+sprinkler.getSensor().getId()+","+sprinkler.getStatus()+")";
		super.update(sql);
	}

	@Override
	public void deleteSprinklerById(Sprinkler sprinkler)
			throws RuntimeException {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM `sprinkler` WHERE id=" + sprinkler.getId();
		super.update(sql);
	}

	@Override
	public boolean updateSprinklerStatusById(Sprinkler sprinkler, String status)
			throws RuntimeException {
		// TODO Auto-generated method stub
		try {
			String sql = "UPDATE `sprinkler` SET status='"
					+ status + "' WHERE name='"+sprinkler.getName()+"'";
			super.update(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean updateSprinklerStatusByGroup(Group group, String status)
			throws RuntimeException {
		// TODO Auto-generated method stub
		try {
			String sql = "UPDATE `sprinkler` SET status='"
					+ status + "' WHERE group_id IN ( SELECT ID FROM `group` WHERE name='"+group.getName()+"')";
			super.update(sql);
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public Sprinkler querySprinklerById(int id) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `sprinkler` WHERE id=" + id;
		Sprinkler sprinkler = null;
		ResultSet rs = super.query(sql);
		try {
			if (rs.next()) {
				sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("id"));
				sprinkler.setName(rs.getString("name"));
				sprinkler.setDescription(rs.getString("description"));
				sprinkler.setCreatetime(rs.getDate("createtime"));
				sprinkler.setStatus(rs.getString("status"));
				Group group=new Group();
				group.setId(rs.getInt("group_id"));
				sprinkler.setGroup(group);
				SprinklerType type=new SprinklerType();
				type.setId(rs.getInt("stype_id"));
				sprinkler.setSprinklerType(type);
				Sensor sensor=new Sensor();
				sensor.setId(rs.getInt("sensor_id"));
				sprinkler.setSensor(sensor);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Sprinkler> queryAllSprinklers()  {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `sprinkler`";
		List<Sprinkler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Sprinkler>();
			while (rs.next()) {
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("id"));
				sprinkler.setName(rs.getString("name"));
				sprinkler.setDescription(rs.getString("description"));
				sprinkler.setCreatetime(rs.getDate("createtime"));
				sprinkler.setStatus(rs.getString("status"));
				Group group=new Group();
				group.setId(rs.getInt("group_id"));
				sprinkler.setGroup(group);
				SprinklerType type=new SprinklerType();
				type.setId(rs.getInt("stype_id"));
				sprinkler.setSprinklerType(type);
				Sensor sensor=new Sensor();
				sensor.setId(rs.getInt("sensor_id"));
				sprinkler.setSensor(sensor);
				list.add(sprinkler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Sprinkler> querySprinklersByGroupId(int groupId){
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `sprinkler` WHERE group_id="+groupId;
		List<Sprinkler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Sprinkler>();
			while (rs.next()) {
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("id"));
				sprinkler.setName(rs.getString("name"));
				sprinkler.setDescription(rs.getString("description"));
				sprinkler.setCreatetime(rs.getDate("createtime"));
				sprinkler.setStatus(rs.getString("status"));
				Group group=new Group();
				group.setId(rs.getInt("group_id"));
				sprinkler.setGroup(group);
				SprinklerType type=new SprinklerType();
				type.setId(rs.getInt("stype_id"));
				sprinkler.setSprinklerType(type);
				Sensor sensor=new Sensor();
				sensor.setId(rs.getInt("sensor_id"));
				sprinkler.setSensor(sensor);
				list.add(sprinkler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public Group queryGroupByName(String name){
		String sql = "SELECT * FROM `group` WHERE name='"+name+"'";
		Group group = null;
		ResultSet rs = super.query(sql);
		try {
			if (rs.next()) {
				group = new Group();
				group.setId(rs.getInt("id"));
				group.setName(rs.getString("name"));
				group.setDescription(rs.getString("description"));
				group.setCreatetime(rs.getDate("createtime"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return group;
	}

	@Override
	public Sprinkler querySprinklerByName(String name){
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `sprinkler` WHERE name='"+name+"'" ;
		Sprinkler sprinkler = null;
		ResultSet rs = super.query(sql);
		try {
			if (rs.next()) {
				sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sprinkler;
	}

	@Override
	public Sprinkler querySprinklerStatus(String name) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `sprinkler` WHERE name='"+name+"'";
		Sprinkler sprinkler = null;
		ResultSet rs = super.query(sql);
		try {
			if (rs.next()) {
				sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("id"));
				sprinkler.setName(rs.getString("name"));
				sprinkler.setDescription(rs.getString("description"));
				sprinkler.setCreatetime(rs.getDate("createtime"));
				sprinkler.setStatus(rs.getString("status"));
				Group group=new Group();
				group.setId(rs.getInt("group_id"));
				sprinkler.setGroup(group);
				SprinklerType type=new SprinklerType();
				type.setId(rs.getInt("stype_id"));
				sprinkler.setSprinklerType(type);
				Sensor sensor=new Sensor();
				sensor.setId(rs.getInt("sensor_id"));
				sprinkler.setSensor(sensor);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Sprinkler> queryAllDisabledSprinklers() {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `sprinkler` WHERE status='Disabled'";
		List<Sprinkler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Sprinkler>();
			while (rs.next()) {
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("id"));
				sprinkler.setName(rs.getString("name"));
				sprinkler.setDescription(rs.getString("description"));
				sprinkler.setCreatetime(rs.getDate("createtime"));
				sprinkler.setStatus(rs.getString("status"));
				Group group=new Group();
				group.setId(rs.getInt("group_id"));
				sprinkler.setGroup(group);
				SprinklerType type=new SprinklerType();
				type.setId(rs.getInt("stype_id"));
				sprinkler.setSprinklerType(type);
				Sensor sensor=new Sensor();
				sensor.setId(rs.getInt("sensor_id"));
				sprinkler.setSensor(sensor);
				list.add(sprinkler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
}
